ANALYTICS ENGINEERING / AVIATION CUSTOMER EXPERIENCE

214,681 reviews.
Governed metrics stakeholders can trust.

AeroVoice turns public aviation reviews — airlines, airports, lounges, seats — into an audited dimensional warehouse with NLP enrichment and a privacy-safe public dashboard, built to answer real stakeholder questions with numbers that can be traced back to tests.

Explore live dashboard ↗ View source code

ROLE

Analytics Engineer

SCOPE

Individual project · Jul 2026

DATA

214,681 aviation reviews · 4 review types

STACK

SQL Server · dbt Core · Airflow · Python NLP · ECharts

AeroVoice live dashboard overview

01 / CONTEXT & PROBLEM

Review data is messy, mixed-grain, and easy to misuse.

Public aviation reviews mix four different things — airline flights, airport experiences, lounges, and seats — each with its own rating structure, categorical quirks, and sample-size traps. Naively averaging them produces confident-looking numbers that mislead: a "top airline" ranking built on 12 reviews, or sentiment scores leaking personally identifiable review text.

The goal: a warehouse where each review type keeps its own grain, metrics are defined once with guardrails, and the public dashboard only ever exposes privacy-safe aggregates — then prove it works by answering 15 concrete stakeholder requests.

02 / ARCHITECTURE

Four grains, one governed pipeline.

Raw review data

airline · airport · lounge · seat

→

Python ingestion + NLP

local sentiment · topic signals · no external APIs

→

dbt on SQL Server: stg → atomic facts → semantic marts

39 models · conformed dimensions · 4 review grains kept separate

→

Quality gates

95 dbt tests · 25 Python contract/unit tests · CI

→

ECharts dashboard

privacy-safe aggregates · GitHub Pages

Airflow provides an audited refresh entrypoint · GitHub Actions runs quality gates on every change

03 / KEY DECISIONS

What I chose, and why

Four separate review grains, not one merged table

Airline, airport, lounge and seat reviews measure different experiences. Keeping atomic facts per grain with conformed dimensions prevents cross-grain averages that look right and mean nothing.

Sample-size guardrails inside metric definitions

Recommendation rate and low-rating rate only surface when an entity clears a minimum review count — defined in the semantic marts, so no downstream chart can rank an airline on a handful of reviews.

Local NLP, zero external calls

Sentiment and topic enrichment run locally in Python — reproducible, free, and no review text ever leaves the machine. The public layer publishes aggregates only.

Stakeholder requests as the acceptance test

15 concrete questions (rankings, drivers of dissatisfaction, coverage) defined the marts — the warehouse was done when every request could be answered from tested models, not when the schema looked elegant.

04 / RESULTS

Measured outcomes

39

dbt models across staging, facts and semantic marts

95 + 25

dbt data tests + Python contract/unit tests in CI

15/15

stakeholder requests answered from governed marts

0

raw review texts exposed on the public dashboard

05 / WHAT I'D DO DIFFERENTLY

Define the semantic layer before the first model.

I wrote several staging models before locking metric definitions, and reworked them once guardrails changed what the marts needed. Next time: agree the governed metrics and their minimum-sample rules first, then let them dictate the model DAG.

I'd also add incremental loading — the full-refresh pattern is fine at 214K reviews but wouldn't survive a continuously growing source.